{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7d2e88f1-c36d-4f4e-9902-3dafdcbbf618",
   "metadata": {},
   "source": [
    "## Pandas\n",
    "很好的支持金融数据和时序数据的分析\n",
    "\n",
    "### 序列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecad4132-aa65-4f74-a8bc-1da9f42f32f2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-25T03:13:30.505885400Z",
     "start_time": "2024-11-25T03:13:30.459136600Z"
    }
   },
   "outputs": [],
   "source": [
    "from pandas import Series\n",
    "\n",
    "x = Series(['a', 2, '西瓜'], index=[1,2,3], name='ts')    # index默认索引0开始, 也可使用其他字符串, name指定列名\n",
    "x[2]  # 索引访问"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8bcd7f4-c8cb-4047-8923-58f318359c54",
   "metadata": {},
   "outputs": [],
   "source": [
    "x[1:-1]  # 切片和numpy一样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "109b0b2f-14c9-4901-b8d3-da327a6229e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "x[[1,2]]  # 索引抽样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b21ec1b8-d15c-467a-aa7c-18034489887a",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = Series(['a', 2, '西瓜'], index=['东', '南', '西']) \n",
    "x[['西', '南']]  #索引抽样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a411fd3-ed81-4712-b2d8-389a77707c7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = Series([5, 8, 99], index=['x', 'y', 'z'])\n",
    "x1 = x.drop('z')  # 只能使用index指定的索引\n",
    "x1, x, x.index[0]  # index 按索引号获取索引名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c5a3da6-0dcc-49cf-9820-45908dab546c",
   "metadata": {},
   "outputs": [],
   "source": [
    "x1 = x[8!=x.values]  # 条件筛选\n",
    "x,x1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "627ed7e5-3907-48d5-9686-211f13ba7fd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "x[x.index[x.values > 10]] = 0  # 条件索引查询 \n",
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb28821b-80e4-4747-a873-f8ddbe026484",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = Series([5, 8, 99], index=['x', 'n', 'g'])\n",
    "x.sort_index(ascending=True), x.reindex(index=['g', 'x', 'n'])  # 索引排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efbd61a0-1cd5-49f0-ad76-6b4e74e84e50",
   "metadata": {
    "ExecuteTime": {
     "start_time": "2024-11-25T03:13:30.497880200Z"
    }
   },
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "\n",
    "df = DataFrame({'age': Series([18, 27, 30]), 'name': Series(['Jerry', 'Tom', 'Kitty'])}, index=[0,1,2])  # index 用数字, str测试会导致列表值为NAN\n",
    "df, df['name'], df[0:-1], df[2:]  # 列名取列, 行号范围取行(必须使用范围表达式)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b0c5c43-5b21-4283-ab4d-4d71cbbca52f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[1:,0:], df.loc[2:, :'name'] # iloc行列号范围访问块, loc 行列索引范围访问块"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5052f725-d18e-4cf4-81cf-5064441a3ed8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.at[2, 'name']  # 行号列名访问cell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "836a1ebc-6175-4831-af3c-0a81651b58c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.columns[0:1]]  # 列索引访问"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb309f0b-bbc1-4b40-aa80-df74db42329b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(2),  df.tail(2) # 头几行, 尾几行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a985a7b-ec79-46e4-b0b8-57850d88865f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import read_csv\n",
    "import efinance as ef\n",
    "\n",
    "df = DataFrame(ef.stock.get_quote_history('601225'))\n",
    "df.to_csv('601225.csv', index=False)\n",
    "\n",
    "gp = read_csv('601225.csv') # or read_table\n",
    "gp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26c7a08a-ff36-442a-8d1c-e60db559a9b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import read_excel\n",
    "\n",
    "gp.to_excel('601225.xlsx', sheet_name='gp', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01cb3a57-75cc-4a03-9e76-3c0213fa1ecc",
   "metadata": {},
   "outputs": [],
   "source": [
    "xl = read_excel('601225.xlsx', sheet_name='gp', skiprows=[2, 3]).head(20)\n",
    "xl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92645058-cb33-4416-9ec8-bfafc45f3a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# value_counts\n",
    "\n",
    "xl['涨跌额'].value_counts()  # 统计列唯一值出现次数\n",
    "xl['涨跌额'].value_counts(ascending=True)  # 统计列唯一值出现次数, 按照次数顺序升序\n",
    "xl['涨跌额'].value_counts(dropna=True).sort_index(ascending=False)  # 统计列唯一值出现次数, 舍弃nan值, 按照列值倒序\n",
    "xl['涨跌额'].value_counts(normalize=True)   # 归一化\n",
    "xl.groupby('涨跌额')['日期'].value_counts()  # 多字段分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcdab960-337b-4053-ac6f-09eeae805a00",
   "metadata": {},
   "outputs": [],
   "source": [
    "xl[~(xl['涨跌额'] > 0.1)]  # 条件取反"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f68b063f-4494-4afc-bae1-fe3053068580",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x)\n",
    "xl.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "185c7f37-b54a-43a3-8109-66586a504c95",
   "metadata": {},
   "outputs": [],
   "source": [
    "xl.T"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
